package csv;

import 实体.dbutils;
import 实体.会员卡;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Dao {

    public List<Object[]> select会员卡() throws SQLException{
        List<Object[]> ls = new ArrayList<Object[]>();
        // 省略连接数据库和获取数据
        Connection conn=dbutils.getCon();
        PreparedStatement stmt=null;
        ResultSet res=null;
        stmt=conn.prepareStatement("select * from 会员卡");
        res=stmt.executeQuery();
        ls=result会员卡(conn,stmt,res);
        stmt=conn.prepareStatement("select count(*) from 会员卡");
        res=stmt.executeQuery();
        Object[] result = new Object[6];
        result[0]="";
        result[1]="";
        result[2]="";
        result[3]="";
        result[4]="总数：";
        while (res.next()){
            result[5]=res.getString(1);
        }
        ls.add(result);
        dbutils.close(conn,stmt,res);
        return ls;
    }
    public List<Object[]> result会员卡(Connection conn,PreparedStatement stmt,ResultSet res) throws SQLException{
        List<Object[]> list=new ArrayList<>();
        try{
            while(res.next()){
                Object[] result = new Object[6];
                result[0]=res.getString("卡号");
                result[1]=res.getString("姓名");
                result[2]=res.getString("类型");
                result[3]=res.getString("余额");
                result[4]=res.getString("折扣");
                result[5]=res.getString("状态");
                list.add(result);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return list;
    }


    public List<Object[]> select消费记录() throws SQLException{
        List<Object[]> ls = new ArrayList<Object[]>();
        // 省略连接数据库和获取数据
        Connection conn=dbutils.getCon();
        PreparedStatement stmt=null;
        ResultSet res=null;
        stmt=conn.prepareStatement("select * from 消费记录");
        res=stmt.executeQuery();
        ls=result消费记录(conn,stmt,res);
        stmt=conn.prepareStatement("select sum(消费金额),sum(存款金额) from 消费记录");
        res=stmt.executeQuery();
        Object[] result = new Object[6];
        result[0]="";
        result[1]="消费金额：";

        result[5]="";
        result[3]="存款金额：";
        while (res.next()){
            result[2]=res.getString(1);
            result[4]=res.getString(2);
        }
        ls.add(result);
        dbutils.close(conn,stmt,res);
        return ls;
    }
    public List<Object[]> result消费记录(Connection conn,PreparedStatement stmt,ResultSet res) throws SQLException{
        List<Object[]> list=new ArrayList<>();
        try{
            while(res.next()){
                Object[] result = new Object[6];
                result[0]=res.getString("卡号");
                result[1]=res.getString("姓名");
                result[2]=res.getString("消费时间");
                result[3]=res.getString("消费金额");
                result[4]=res.getString("存款金额");
                result[5]=res.getString("结存余额");
                list.add(result);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return list;
    }



}
